<?php

/**
 * @auth yxt
 * 数据库语句拼装操作
 */
class nf_logic_base
{

    public static $arr_sql=array();
    public $dc = null;
    public $arr_rules = null;
    public $db = null;
    public $str_sql = null;
    public $str_tab_name = ""; //表名，自动获取
    public $str_from = "";
    public $arr_field_list = "*";
    public $str_on = ''; //连接查询的on条件
    public $int_insert_id = 0; //对于插入的时候插入的记录auto_increment 产生的id
    //SQL的初使化
    public $arr_sql_statement = array("field" => "", "where" => "", "order" => "", "limit" => "", "group" => "", "having" => "");

    public final function set_dc($dc)
    {
        $this->dc = $dc;
        $this->db = $dc->db();
    }

    /**
     * 用来获取表名
     */
    public function __get($pro)
    {
        if ($pro == "str_tab_name") {
            return $this->str_tab_name;
        }
    }

    /**
     * 连贯操作调用field() where() order() limit() group() having()方法，组合SQL语句
     */
    function __call($str_method_name, $arr_args)
    {
        $str_method_name = strtolower($str_method_name);
        if (array_key_exists($str_method_name, $this->arr_sql_statement)) {
            if (empty($arr_args[0]) || (is_string($arr_args[0]) && trim($arr_args[0]) === '')) {
                $this->arr_sql_statement[$str_method_name] = "";
            } else {
                $this->arr_sql_statement[$str_method_name] = $arr_args;
            }

            if ($str_method_name == "limit") {
                $this->arr_sql_statement[$str_method_name] = $arr_args[0];
            }
        }
        return $this;
    }

    /**
     * 按指定的条件获取结果集中的记录数
     */
    protected final function total()
    {
        $str_where = "";

        if ($this->arr_sql_statement["where"] != "") {
            $str_where = $this->com_where($this->arr_sql_statement["where"]);
            $str_where = $str_where["where"];
        }
        $arr_re = array('ret' => 1, 'reason' => 'success', 'data' => array());
        $str_sql = "SELECT COUNT(1) as total FROM {$this->str_from}{$str_where}";
        $re = $this->query($str_sql);
        if ($re === false) {
            $arr_re['ret'] = $this->last_error_no();
            $arr_re['reason'] = $this->last_error_desc();
            $arr_re['data'] = array();
        } elseif (is_array($re)) {
            $arr_re['data'] = !is_array($re) ? array() : $re[0];
        }
        return $arr_re;
    }

    /**
     * 获取查询多条结果，返回二维数组
     * @param boolean $bool_is_total 是否计算总数，默认是要计算的
     * @return array $arr_re 返回操作结果数组array('ret'=>1查询成功，其余失败，'reason'=>'说明信息','data'=>结果集数组，如果数组为空则表示没有数据)
     * @注意结果集是否为空要用data来判断，ret只是表示查询是否成功
     */
    protected final function select($bool_is_total = true)
    {
        if (empty($this->str_from)) {
            $this->str_from = $this->str_tab_name;
        }
        //返回的结构数据
        $arr_re = array('ret' => 1, 'reason' => 'success', 'data' => array());
        $str_fields = $this->arr_sql_statement["field"] != "" ? $this->arr_sql_statement["field"][0] : $this->arr_field_list;

        $str_where = "";

        if ($this->arr_sql_statement["where"] != "") {
            $str_where = $this->com_where($this->arr_sql_statement["where"]);
            $str_where = $str_where['where'];
        }

        $str_order = $this->arr_sql_statement["order"] != "" ? " ORDER BY {$this->arr_sql_statement["order"][0]}" : "";
        $str_limit = $this->arr_sql_statement["limit"] != "" ? $this->com_limit($this->arr_sql_statement["limit"]) : "";
        $str_group = $this->arr_sql_statement["group"] != "" ? " GROUP BY {$this->arr_sql_statement["group"][0]}" : "";
        $str_having = $this->arr_sql_statement["having"] != "" ? " HAVING {$this->arr_sql_statement["having"][0]}" : "";

        $str_sql = "SELECT {$str_fields} FROM {$this->str_from}{$str_where}{$str_group}{$str_having}{$str_order}{$str_limit}";
        $re = $this->query($str_sql);
        if ($re === false) {
            $arr_re['ret'] = $this->last_error_no();
            $arr_re['reason'] = $this->last_error_desc();
            $arr_re['data']['list'] = array();
        } else {
            $arr_re['data']['list'] = !is_array($re) ? array() : $re;
            if ($bool_is_total) {
                $str_sql = "SELECT count(1) as total FROM {$this->str_from}{$str_where}{$str_group}{$str_having}{$str_order}";
                $re_total = $this->query($str_sql, __METHOD__);
                $arr_re['data']['total'] = $re_total[0]['total'];
            }
        }
        $this->str_from=null;
        $this->arr_sql_statement = array("field" => "", "where" => "", "order" => "", "limit" => "", "group" => "", "having" => "");
        return $arr_re;
    }

    /**
     * 获取一条记录，返回一维数组
     */
    protected final function find()
    {
        if (empty($this->str_from)) {
            $this->str_from = $this->str_tab_name;
        }
        $arr_re = array('ret' => 1, 'reason' => 'success', 'data' => array());
        $str_fields = $this->arr_sql_statement["field"] != "" ? $this->arr_sql_statement["field"][0] : $this->arr_field_list;
        $str_where = '';
        if ($this->arr_sql_statement["where"] != "") {
            $str_where = $this->com_where($this->arr_sql_statement["where"]);
            $str_where = $str_where['where'];
        }
        $str_order = $this->arr_sql_statement["order"] != "" ? " ORDER BY {$this->arr_sql_statement["order"][0]}" : "";
        $str_sql = "SELECT {$str_fields} FROM {$this->str_from}{$this->str_on}{$str_where}{$str_order} LIMIT 1";
        $re = $this->query($str_sql);
        if ($re === false) {
            $arr_re['ret'] = $this->last_error_no();
            $arr_re['reason'] = $this->last_error_desc();
            $arr_re['data'] = array();
        } else {
            $arr_re['data'] = isset($re[0]) && is_array($re[0]) ? $re[0] : array();
        }
        $this->str_from=null;
        $this->arr_sql_statement = array("field" => "", "where" => "", "order" => "", "limit" => "", "group" => "", "having" => "");
        return $arr_re;
    }

    //filter = 1 转义 " ' 和 HTML 实体， 0则不变
    private function filter(&$arr_data, $filter)
    {
        $key_flag = array_rand($arr_data);
        //如果是二维数组
        if (is_array($arr_data[$key_flag])) {
            foreach ($arr_data as &$arr_rows) {
                $this->_filter($arr_rows, $filter);
            }
        } else {
            $this->_filter($arr_data, $filter);
        }
    }

    private function _filter(&$arr_data, $filter)
    {
        $arr_re = array();
        foreach ($arr_data as $key => &$value) {
            if(!is_string($value)){
                continue;
            }
            $key = strtolower($key);
            if (is_array($filter) && !empty($filter)) {
                if (in_array($key, $filter)) {
                    $value = addslashes(htmlspecialchars($value, ENT_NOQUOTES));
                }
            } else if (!$filter) {
                $value = $value;
            } else {
                $value = addslashes(htmlspecialchars($value, ENT_NOQUOTES));
            }
        }
    }

    /**
     * 插入一条数据
     * @param array $arr_insert_data ,插入的数据数组,如果插入一行为一维数组，如果插入多行则为二维数组（字段是随机选取的）
     * @param bool $bool_filter ,是否过滤，默认需要过滤
     * @param bool $bool_validata 是否需要验证字段,默认不验证
     * @return array $arr_re 返回操作结果数组array('ret'=>1成功，其余失败，'reason'=>'说明信息','data'=>成功返回插入成功的行数)
     */
    protected final function replace($arr_insert_data = null, $bool_filter = 1, $bool_validata = false)
    {
        return $this->_replace_insert($arr_insert_data, $bool_filter, $bool_validata, 0);
    }

    private function _replace_insert($arr_insert_data = null, $bool_filter = 1, $bool_validata = false, $str_type = 1)
    {
        if (empty($this->str_from)) {
            $this->str_from = $this->str_tab_name;
        }
        //返回的结构数据
        $arr_re = array('ret' => 1, 'reason' => 'success', 'data' => array());
        if (empty($arr_insert_data)) {
            $arr_re['ret'] = 0;
            $arr_re['reason'] = 'data can not empty';
            return $arr_re;
        }
        //是否需要验证字段数据类型
        if ($bool_validata) {
            $arr_re = nf_validate::check($arr_insert_data, 'insert', $this);
            if ($arr_re['ret'] != 1) {
                return $arr_re;
            }
        }
        //判断是否
        if ($bool_filter) {
            $this->filter($arr_insert_data, $bool_filter);
        }
        $key_flag = array_rand($arr_insert_data);
        if ($str_type == 1) {
            $str_pre = 'INSERT';
        } else {
            $str_pre = 'REPLACE';
        }
        //如果是数组，则表示的批量插入
        if (is_array($arr_insert_data[$key_flag])) {
            $str_sql = "{$str_pre} INTO {$this->str_from}(" . implode(',', array_keys($arr_insert_data[$key_flag])) . ") VALUES ";
            foreach ($arr_insert_data as $rows) {
                $str_val='(';
                foreach($rows as $_val){
                    if(is_string($_val)){
                        $str_val.="'{$_val}',";
                    }else{
                        $str_val.="{$_val},";
                    }
                }
                $str_val=rtrim($str_val,',').'),';
                $str_sql .= $str_val;
            }
            $str_sql = rtrim($str_sql, ',');
        } else { //如果是插入一行
            $str_sql = "{$str_pre} INTO {$this->str_from}(" . implode(',', array_keys($arr_insert_data)) . ") VALUES (";
            $str_val="";
            foreach($arr_insert_data as $_val){
                if(is_string($_val)){
                    $str_val.="'{$_val}',";
                }else{
                    $str_val.="{$_val},";
                }
            }
            $str_val=rtrim($str_val,',').')';
            $str_sql.=$str_val;
        }
        $re = $this->query($str_sql);
        if ($re === false) {
            $arr_re['ret'] = $this->last_error_no();
            $arr_re['reason'] = $this->last_error_desc();
            $arr_re['data'] = array();
        } elseif (is_array($re)) {
            $arr_re['data'] = $re;
        }
        return $arr_re;
    }

    /**
     * 插入一条数据
     * @param array $arr_insert_data ,插入的数据数组,如果插入一行为一维数组，如果插入多行则为二维数组（字段是随机选取的）
     * @param bool $bool_filter ,是否过滤，默认需要过滤
     * @param bool $bool_validata 是否需要验证字段,默认不验证
     * @return array $arr_re 返回操作结果数组array('ret'=>1成功，其余失败，'reason'=>'说明信息','data'=>成功返回插入成功的行数)
     */
    protected final function insert($arr_insert_data = null, $bool_filter = true, $bool_validata = false)
    {
        return $this->_replace_insert($arr_insert_data, $bool_filter, $bool_validata);
    }

    /**
     * 更新数据表中指定条件的记录
     * @param array $arr_insert_data ,插入的数据数组,如果插入一行为一维数组，如果插入多行则为二维数组（字段是随机选取的）
     * @param mixed $filter ,是否过滤，默认需要过滤
     * @param bool $bool_validata 是否需要验证字段
     * @return array $arr_re 返回操作结果数组array('ret'=>1成功，其余失败，'reason'=>'说明信息','data'=>成功返回修改成功的行数，如果=0表示没有做任何修改)
     */
    protected final function update($arr_update_data = null, $filter = 1, $bool_validata = false)
    {
        if (empty($this->str_from)) {
            $this->str_from = $this->str_tab_name;
        }
        $arr_re = array('ret' => 1, 'reason' => 'success', 'data' => array());
        //如果为空则返回false
        if (empty($arr_update_data)) {
            $arr_re['ret'] = 0;
            $arr_re['reason'] = 'data can not empty';
            return $arr_re;
        }
        //是否需要验证字段数据类型
        if ($bool_validata) {
            $arr_re = nf_validate::check($arr_update_data, 'update', $this);
            if ($arr_re['ret'] != 1) {
                return $arr_re;
            }
        }
        //判断是否对字段进行检查
        if ($filter) {
            $this->filter($arr_update_data, $filter);
        }
        //判断参数是否是数组
        if (is_array($arr_update_data)) {
//            if (array_key_exists($this->arr_field_list["pri"], $arr_update_data)) {
//                $pri_value = $arr_update_data[$this->arr_field_list["pri"]];
//                unset($arr_update_data[$this->arr_field_list["pri"]]);
//            }
            $setfield = '';
            foreach ($arr_update_data as $k => $v) {
                if (is_string($v)) {
                    $setfield .= "{$k}='{$v}',";

                } else {
                    $setfield .= "{$k}={$v},";
                }
            }
            $setfield = rtrim($setfield, ",");
        } else {
            $setfield = $arr_update_data;
        }
        //判断是否有where条件
        if ($this->arr_sql_statement["where"] != "") {
            $str_where = $this->com_where($this->arr_sql_statement["where"]);
            $str_sql = "UPDATE  {$this->str_from} SET {$setfield}" . $str_where["where"];
            $str_order = $this->arr_sql_statement["order"] != "" ? " ORDER BY {$this->arr_sql_statement["order"][0]}" : "";
            $str_limit = $this->arr_sql_statement["limit"] != "" ? $this->com_limit($this->arr_sql_statement["limit"]) : "";
            $str_sql .= $str_order . $str_limit;
        } else {
            $str_sql = "UPDATE {$this->str_from} SET {$setfield}";
        }
        $re = $this->query($str_sql);
        if ($re === false) {
            $arr_re['ret'] = $this->last_error_no();
            $arr_re['reason'] = $this->last_error_desc();
            $arr_re['data'] = array();
        }
        return $arr_re;
    }

    /**
     * 删除满足条件的记录
     */
    protected final function delete()
    {
        $arr_re = array('ret' => 1, 'reason' => 'success', 'data' => array());
        if (empty($this->str_from)) {
            $this->str_from = $this->str_tab_name;
        }
        $str_where = "";
        if ($this->arr_sql_statement["where"] != "") {
            $str_where = $this->com_where($this->arr_sql_statement["where"]);
            $str_where = $str_where["where"];
        }
        $str_order = $this->arr_sql_statement["order"] != "" ? " ORDER BY {$this->arr_sql_statement["order"][0]}" : "";
        $str_limit = $this->arr_sql_statement["limit"] != "" ? $this->com_limit($this->arr_sql_statement["limit"]) : "";
        $str_sql = "DELETE FROM {$this->str_from}{$str_where}{$str_order}{$str_limit}";
        $bool_re = $this->query($str_sql);
        //如果删除成功
        if ($bool_re) {
//            $arr_re['data'] = mysql_affected_rows($this->db);
        } else {
            $arr_re['ret'] = $this->last_error_no();
            $arr_re['reason'] = $this->last_error_desc();
        }
        return $arr_re;
    }

    /**
     *
     * @param mixed $args ，参数可以是数组也可以是字符串
     * @return string
     */
    private function com_limit($args)
    {
        if (empty($args)) {
            return '';
        }
        if (is_string($args)) {
            return " LIMIT {$args}";
        }
        if (count($args) == 2) {
            return " LIMIT {$args[0]},{$args[1]}";
        } else if (count($args) == 1) {
            return " LIMIT {$args[0]}";
        } else {
            return '';
        }
    }

    /**
     * 用来组合SQL语句中的where条件 ，目前只支持字符串和数组的and查询
     */
    private function com_where($args)
    {
        $args = $args[0];
        if (empty($args)) {
            return array("where" => "");
        } elseif (is_array($args)) { //如果是数组
            foreach ($args as &$_v1) {
                if (is_array($_v1)) {
                    foreach ($_v1 as &$_v2) {
                        if (is_array($_v2)) {
                            foreach ($_v2 as &$_v3) {
                                if (is_string($_v3)) {
                                    $_v3 = addslashes($_v3);
                                }
                            }
                        } else {
                            if (is_string($_v2)) {
                                $_v2 = addslashes($_v2);
                            }
                        }
                    }
                } elseif (is_string($_v1)) {
                    $_v1 = addslashes($_v1);
                }
            }
            $str_where = " where ";
            foreach ($args as $field => $val) {
                if (strpos($field, ' ') !== false) {
                    $_arr_fields = explode(' ', $field);
                    switch (strtolower($_arr_fields[1])) {
                        case 'in':
                            if (count($val) > 1) {
                                if (is_string($val[0])) {
                                    $str_in = implode("','", $val);
                                    $str_in="'".$str_in."'";
                                } else {
                                    $str_in = implode(",", $val);
                                }
                            } else {
                                if (is_string($val[0])) {
                                    $str_in = "'" . $val[0] . "'";
                                } else {
                                    $str_in = $val[0];
                                }
                            }
                            $str_where .= "{$_arr_fields[0]} {$_arr_fields[1]} ({$str_in}) AND ";
                            break;
                        case 'notin':
                            if (count($val) > 1) {
                                if (is_string($val[0])) {
                                    $str_in = implode("','", $val);
                                    $str_in="'".$str_in."'";
                                } else {
                                    $str_in = implode(",", $val);
                                }
                            } else {
                                if (is_string($val[0])) {
                                    $str_in = "'" . $val[0] . "'";
                                } else {
                                    $str_in = $val[0];
                                }
                            }
                            $str_where .= "{$_arr_fields[0]} NOT IN ({$str_in}) AND ";
                            break;
                        default:
                            if (is_string($val)) {
                                $val = "'" . $val . "'";
                            }
                            $str_where .= "{$_arr_fields[0]} {$_arr_fields[1]} {$val} AND ";
                            break;
                    }
                } else {
                    if (is_string($val)) {
                        $str_where .= "{$field}='{$val}' AND ";
                    } else {
                        $str_where .= "{$field}={$val} AND ";
                    }
                }
            }
            $str_where = rtrim($str_where);
            $str_where = rtrim($str_where, 'AND');
            return array('where' => $str_where);
        } elseif (is_string($args)) { //如果是字符串
            return array("where" => ' where ' . $args);
        } else {
            return array("where" => "");
        }
    }

    protected final function join($str_table)
    {
        $this->str_from .= ' ' . $str_table;
        return $this;
    }

    protected final function query($str_sql,$str_method=null)
    {
        $this->str_sql = $str_sql;
        $arr_sql = explode(' ', $str_sql);
        $str_sql_type = strtolower($arr_sql[0]);
        if (in_array($str_sql_type, array("insert", "delete", "update", "replace"))) {
            $arr_data = nl_execute_by_db($str_sql, $this->db);
        } else {
            $arr_data = nl_query_by_db($str_sql, $this->db);
        }
        if ($arr_data !== false) {
            $str_sql = "[success]" . $this->str_sql;
        } else {
            $str_sql = "[error]" . $this->str_sql;
        }
        self::$arr_sql[]=$str_sql;
//        $this->str_from=null;
        return $arr_data;
    }

    protected final function table($str_tab_name)
    {
        $this->str_from = $str_tab_name;
        return $this;
    }

    private function last_error_no()
    {
        return $this->db->last_error_no();
    }

    private function last_error_desc()
    {
        return $this->db->last_error_desc();
    }

}
